---
title: Formula
---

import FormulaList from '@/components/formula-list'

Formulas are one of the core capabilities provided by Univer, allowing you to use formulas in cells to calculate values. The functions supported by formulas are consistent with Excel, including mathematical, logical, text, date functions, and more.

## Configuration

Following are the links to the configuration items related to the formula plugin:

| Plugin | Configuration Item |
| --- | --- |
| `@univerjs/engine-formula` | [IUniverEngineFormulaConfig](https://github.com/dream-num/univer/blob/dev/packages/engine-formula/src/controller/config.schema.ts#L29) |
| `@univerjs/sheets-formula` | [IUniverSheetsFormulaBaseConfig](https://github.com/dream-num/univer/blob/dev/packages/sheets-formula/src/controllers/config.schema.ts#L44) |

### Presets Configuration

```typescript
import type { CalculationMode } from '@univerjs/preset-sheets-core'
import { UniverSheetsCorePreset } from '@univerjs/preset-sheets-core'

interface IUniverSheetsCorePresetConfig {
  formula?: {
    // Custom formula functions
    function?: Array<[Ctor<BaseFunction>, IFunctionNames]>
    // Custom formula descriptions
    description?: IFunctionInfo[]
    // Defines the calculation mode for formulas during data initialization, default is `WHEN_EMPTY`
    initialFormulaComputing?: CalculationMode
  }
}
```

### Plugin Configuration

```typescript
import type { CalculationMode } from '@univerjs/sheets-formula'
import { UniverSheetsFormulaPlugin } from '@univerjs/sheets-formula'

interface IUniverSheetsFormulaConfig {
  // Custom formula functions
  function?: Array<[Ctor<BaseFunction>, IFunctionNames]>
  // Custom formula descriptions
  description?: IFunctionInfo[]
  // Defines the calculation mode for formulas during data initialization, default is `WHEN_EMPTY`
  initialFormulaComputing?: CalculationMode
}
```

The definition of `CalculationMode` is as follows:

```typescript
enum CalculationMode {
  /**
   * Force calculation of all formulas
   */
  FORCED,

  /**
   * Partial calculation, only calculates cells with formulas but no value
   */
  WHEN_EMPTY,

  /**
   * No calculation for all formulas
   */
  NO_CALCULATION,
}
```

> If the cell containing the formula has an unexpected value, you can set `initialFormulaComputing` to `FORCED` to force the calculation of all formulas. Alternatively, you can clear the cell's value before initialization.

## Supported Formula Functions

<FormulaList lang="en-US" />

## Facade API

Before using the formula API, make sure to import the `@univerjs/sheets-formula/facade`.

```typescript
import '@univerjs/sheets-formula/facade'
```

### Execute Calculation

```typescript
const formula = univerAPI.getFormula()
formula.executeCalculation()
```

### Stop Calculation

```typescript
const formula = univerAPI.getFormula()
formula.stopCalculation()
```

### Calculation Start Event

```typescript
const formula = univerAPI.getFormula()
formula.calculationStart((forceCalculate) => {
  console.log(forceCalculate)
})
```

### Calculation Processing Event

```typescript
const formula = univerAPI.getFormula()
formula.calculationProcessing((stageInfo) => {
  console.log(stageInfo)
})
```

### Calculation End Event

```typescript
const formula = univerAPI.getFormula()
formula.calculationEnd((functionsExecutedState) => {
  console.log(functionsExecutedState)
})
```

### Set Initial Calculation Mode

```typescript
const formula = univerAPI.getFormula()
formula.setInitialFormulaComputing(CalculationMode.FORCED)
```

If this API is called after the `Starting` lifecycle, it will take effect the next time a Univer Sheet is created.

If you want it to take effect during the current initialization of the Univer Sheet, you can consider calling it before the `Ready` lifecycle, for example:

```typescript
univerAPI.addEvent(univerAPI.Event.LifeCycleChanged, ({ stage }) => {
  if (stage === LifecycleStages.Starting) {
    const formula = univerAPI.getFormula()
    formula.setInitialFormulaComputing(CalculationMode.FORCED)
  }
})
```

## Custom Formulas

Univer supports custom formulas. Please refer to the [Custom Formulas](/guides/recipes/tutorials/custom-formula) section to learn how to implement them.

## Frequently Asked Questions

### `HYPERLINK` Formula Cells Cannot Operate Hyperlinks

You need to import the hyperlink plugin for Univer Sheets. See the [Hyperlink](/guides/sheets/features/hyper-link) module.
